package com.wss.dao;

import com.wss.domain.Account;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 帐户dao接口
 */
@Repository
public interface AccountDao {

    /**
     * 查询所有账户，以及账户权限ID和权限名及描述
     * @return
     */
    @Select("select a.id as id,username,password," +
            "p.id as p_id,p.pname as p_name,p.pdesc as p_desc" +
            " from account as a inner join permissions as p on (a.p_id = p.id) order by a.id")
    List<Account> findAll();

    /**
     * 保存帐户信息 后面的数据变量名应和bean类相同
     * @param account
     * @return
     */
    @Insert("insert into account (username,password,p_id) values (#{username},#{password},#{p_id})")
    int addUser(Account account);

    /**
     * 注册时查询该用户名是否已存在
     * @param username
     * @return
     */
    @Select("select count(*) from account where username = #{username}")
    int checkUsername(String username);

    /**
     * 修改时查询除自己以外该用户名是否已存在
     * @param account
     * @return
     */
    @Select("select count(*) from account where username = #{username} and id != #{id}")
    int checkOtherName(Account account);

    /**
     * 修改用户信息
     * @param account
     * @return
     */
    @Update("update account set username = #{username},password = #{password},p_id = #{p_id} where id=#{id}")
    int updateUser(Account account);

    /**
     * 普通用户修改用户名和密码
     * @param account
     * @return
     */
    @Update("update account set username = #{username},password = #{password} where id=#{id}")
    int updateUserInfo(Account account);

    /**
     * 修改密码前查询原始密码是否输入正确
     * @param account
     * @return
     */
    @Select("select count(*) from account where password=#{password} and id=#{id}")
    int selectAdminPwd(Account account);

    /**
     * 修改管理员密码
     * @param account
     * @return
     */
    @Update("update account set password = #{password} where id=#{id}")
    int changeAdminPwd(Account account);

    /**
     * 删除用户时将删除所有此用户下的blog,comment,order
     * SET FOREIGN_KEY_CHECKS = 0;在删除时有外键关联会有数据库异常，关闭外键关联再执行语句，但在注解中不知道怎么实现
     * 解决方案为在MySQL中设置外键时删除时和更新时的参数选择为CASCADE，级联操作，就可以删除外键关联的表数据了
     * 调整为先删除与用户表关联的其他表内容，最后再删除用户表数据
     * @param id
     * @return
     */
    @Delete("delete blog from account LEFT JOIN blog ON account.id=blog.u_id WHERE account.id=#{id}")
    int deleteBlogWithUid(int id);
    @Delete("delete comments from account LEFT JOIN comments ON account.id=comments.u_id WHERE account.id=#{id}")
    int deleteCommentsWithUid(int id);
    @Delete("delete orders from account LEFT JOIN orders ON account.id=orders.u_id WHERE account.id=#{id}")
    int deleteOrdersWithUid(int id);
    @Delete("delete account from account WHERE account.id=#{id}")
    int deleteUser(int id);

    /**
     * 删除管理员用户前检查是否有其他管理员用户，如果没有不能删除
     * @return
     */
    @Select("select count(*) from account where p_id = 1")
    int checkAdminCount();

    /**
     * 查询要修改的用户p_id
     * @param account
     * @return
     */
    @Select("select p_id from account where id = #{id}")
    int checkUserPid(Account account);

}
